from tool import *
from myexcel import *
from sql import sqlseverDB

ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)

def main():
    
    datas=getDictDatasByEecel('香港ip(2).xlsx')
    zds=['[Ip]','[HttpPort]','[Socks5Port]','[uid]','[upwd]','[LastTime]']
    zdstr=','.join(zds)
    params=['localhost',None,None,None,None,None]
    wstr=','.join(['?' for _ in params])
    sqlstr=f'''
            insert into hkProxy({zdstr}) values ({wstr})
        '''
    aff1=ms.ExecNoQuery(sqlstr,params)
    
    for data in datas:
        ipstr=data['IP']
        sport=data['sock5']
        hport=data['HTTp']
        uid=data['账号']
        upwd=data['密码']
        ltime=int(datetime.datetime.timestamp(data['过期时间']))
        zds=['[Ip]','[HttpPort]','[Socks5Port]','[uid]','[upwd]','[LastTime]']


        rs=ms.ExecQuery('select count(*) from hkproxy where [ip]=?',(ipstr,))

        if rs[0][0]==0:
            zdstr=','.join(zds)
            params=[ipstr,hport,sport,uid,upwd,ltime]
            wstr=','.join(['?' for _ in params])
            sqlstr=f'''
                    insert into hkProxy({zdstr}) values ({wstr})
                '''
            aff1=ms.ExecNoQuery(sqlstr,params)

            print(f'新增代理:{ipstr},状态:{aff1}')

        else:
            zdstr=','.join([f'{zd}=?' for zd in zds])
            params=[ipstr,hport,sport,uid,upwd,ltime,ipstr]

            sqlstr=f'update hkproxy set {zdstr} where [ip]=?'
            aff2=ms.ExecNoQuery(sqlstr,params)
            print(f'更新代理:{ipstr},状态:{aff2}')

if __name__=='__main__':
    main()